package dao.impl;

import dao.NoticeDao;
import entity.Notice;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import utils.DruidUtils;

import java.sql.Connection;
import java.util.List;

public class NoticeDaoImpl implements NoticeDao {


    @Override
    public Notice queryNewNotice() {
        Connection conn = null;
        try {
            //从连接池获取连接
            conn = DruidUtils.getConnection();
            String s = "SELECT  admin_name, content, datetime FROM notice ORDER BY datetime DESC LIMIT 1;";

            QueryRunner queryRunner = new QueryRunner();
            BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
            List<Notice> query = queryRunner.query(conn, s, new BeanListHandler<>(Notice.class, basicRowProcessor));
            DruidUtils.close(null, conn);
            if (!query.isEmpty()) return query.get(0);
            else return null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //归还连接(没有Statement)
            DruidUtils.close(null, conn);
        }
        return null;
    }

    @Override
    public List<Notice> getAllNotices() {
        Connection conn = null;
        try {
            //从连接池获取连接
            conn = DruidUtils.getConnection();
            String s = "SELECT  admin_name, content, datetime FROM notice ORDER BY datetime DESC;";

            QueryRunner queryRunner = new QueryRunner();
            BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
            List<Notice> query = queryRunner.query(conn, s, new BeanListHandler<>(Notice.class, basicRowProcessor));
            DruidUtils.close(null, conn);
            if (!query.isEmpty()) return query;
            else return null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //归还连接(没有Statement)
            DruidUtils.close(null, conn);
        }
        return null;
    }

    @Override
    public long count(Notice notice) {
        return 0;
    }

    @Override
    public int insert(Notice notice) {
        Connection conn = null;
        try {
            //从连接池获取连接
            conn = DruidUtils.getConnection();
            String s = "insert into notice( admin_id, admin_name, content)values (?,?,?)";

            QueryRunner queryRunner = new QueryRunner();
            int update = queryRunner.update(conn, s, notice.getAdminId(), notice.getAdminName(), notice.getContent());
            DruidUtils.close(null, conn);
            return update;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //归还连接(没有Statement)
            DruidUtils.close(null, conn);
        }
        return 0;
    }


    public int insert1(Notice notice) {
        Connection conn = null;
        try {
            //从连接池获取连接
            conn = DruidUtils.getConnection();
            String s = "CALL InsertNotice  (?, ?, ?)";
            QueryRunner queryRunner = new QueryRunner();
            int update = queryRunner.update(conn, s, notice.getAdminId(), notice.getAdminName(), notice.getContent());
            DruidUtils.close(null, conn);
            return update;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //归还连接(没有Statement)
            DruidUtils.close(null, conn);
        }
        return 0;
    }

    public Notice queryNewNotice1() {
        Connection conn = null;
        try {
            //从连接池获取连接
            conn = DruidUtils.getConnection();
            String s = "CALL GetLatestNotice()";

            QueryRunner queryRunner = new QueryRunner();
            BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
            List<Notice> query = queryRunner.query(conn, s, new BeanListHandler<>(Notice.class, basicRowProcessor));
            DruidUtils.close(null, conn);
            if (!query.isEmpty()) return query.get(0);
            else return null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //归还连接(没有Statement)
            DruidUtils.close(null, conn);
        }
        return null;
    }

    public List<Notice> getAllNotices1() {
        Connection conn = null;
        try {
            //从连接池获取连接
            conn = DruidUtils.getConnection();
            String s = "CALL GetAllNotices();";

            QueryRunner queryRunner = new QueryRunner();
            BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());
            List<Notice> query = queryRunner.query(conn, s, new BeanListHandler<>(Notice.class, basicRowProcessor));
            DruidUtils.close(null, conn);
            if (!query.isEmpty()) return query;
            else return null;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //归还连接(没有Statement)
            DruidUtils.close(null, conn);
        }
        return null;
    }

    @Override
    public int update(Notice notice) {
        return 0;
    }

    @Override
    public int deleteById(Integer id) {
        return 0;
    }
}
